{
 "cells": [
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "# Pandas写出内容",
   "id": "4ff644447a2c9883"
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "## 1. 写出CSV数据",
   "id": "de86b5d36a261b9c"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-09T06:13:42.700523Z",
     "start_time": "2025-09-09T06:13:42.694921Z"
    }
   },
   "cell_type": "code",
   "source": [
    "import pandas as pd\n",
    "from pandas import DataFrame\n",
    "\n",
    "path = 'D:/2506A/monty03/day12/file/'\n",
    "\n",
    "# 创建df\n",
    "data = {\n",
    "    '姓名':['聂茹凤','谭鑫宇','韩耀祖'],\n",
    "    '年龄':[21,22,23],\n",
    "    '地址':['吉林省松原市','吉林省松原市','河北省邯郸市']\n",
    "}\n",
    "df = pd.DataFrame(data)\n",
    "\n",
    "# 写出到csv文件中\n",
    "df.to_csv(path + '学生信息.csv',index=False)  # index=False 不保存索引\n",
    "print('保存完毕')"
   ],
   "id": "f32a267ca47e18b5",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "保存完毕\n"
     ]
    }
   ],
   "execution_count": 2
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "## 解决乱码问题",
   "id": "911a48859be2d9a0"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-09T06:17:21.641680Z",
     "start_time": "2025-09-09T06:17:21.635265Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# 写出到csv文件中 高级参数\n",
    "df.to_csv(path + '学生信息2.csv',index=False,encoding='gbk',sep='/')  # index=False 不保存索引\n",
    "print('保存完毕')"
   ],
   "id": "71b05c123c7aea00",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "保存完毕\n"
     ]
    }
   ],
   "execution_count": 4
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "## 2. 保存到cSV文件中",
   "id": "a57dff5cc1445936"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-09T06:21:39.797235Z",
     "start_time": "2025-09-09T06:21:39.792444Z"
    }
   },
   "cell_type": "code",
   "source": [
    "df.to_csv(path + '学生信息3.csv',index=False,sep='\\t')\n",
    "print('保存完毕')"
   ],
   "id": "c55c2a8433dbf4f1",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "保存完毕\n"
     ]
    }
   ],
   "execution_count": 9
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "## 3. 保存到Excel文件中",
   "id": "d4eda10717fbb89"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-09T06:50:10.347120Z",
     "start_time": "2025-09-09T06:50:10.333245Z"
    }
   },
   "cell_type": "code",
   "source": [
    "data = {\n",
    "    '姓名':['聂茹凤','谭鑫宇','韩耀祖'],\n",
    "    '年龄':[21,22,23],\n",
    "    '地址':['吉林省松原市','吉林省松原市','河北省邯郸市']\n",
    "}\n",
    "df = pd.DataFrame(data)\n",
    "df.to_excel(path + '学生信息4.xlsx',index=False)"
   ],
   "id": "1074b6f2909f6a5d",
   "outputs": [],
   "execution_count": 27
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-09T06:26:04.253047Z",
     "start_time": "2025-09-09T06:26:04.240981Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# 新建空白的Excel\n",
    "df = pd.DataFrame()\n",
    "df.to_excel(path + '明星.xlsx')\n",
    "print('空白文件创建成功')"
   ],
   "id": "5064849bce7af74f",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "空白文件创建成功\n"
     ]
    }
   ],
   "execution_count": 13
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-09T06:31:21.828951Z",
     "start_time": "2025-09-09T06:31:21.813613Z"
    }
   },
   "cell_type": "code",
   "source": [
    " # 往新建的空白Excel中写入数据\n",
    " data = {\n",
    "     'id':[1,2,3],\n",
    "     '姓名':['刘亦菲','王丽坤','迪丽热巴']\n",
    " }\n",
    " df = pd.DataFrame(data)\n",
    "\n",
    " # 将id设置为索引\n",
    " df.set_index('id',inplace=True)\n",
    "\n",
    " df.to_excel(path + '明星.xlsx')\n",
    " print('保存成功')"
   ],
   "id": "be611076307cd1d2",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "保存成功\n"
     ]
    }
   ],
   "execution_count": 17
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "## 4. Excel中多工作表导出",
   "id": "80ff8857edd2c30e"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-09T06:36:02.345186Z",
     "start_time": "2025-09-09T06:36:02.327219Z"
    }
   },
   "cell_type": "code",
   "source": [
    " # 准备两个df\n",
    " data1 = {\n",
    "     'id':[1,2,3],\n",
    "     '姓名':['刘亦菲','王丽坤','迪丽热巴']\n",
    " }\n",
    " df1 = pd.DataFrame(data1)\n",
    "\n",
    " data2 = {\n",
    "    '姓名':['聂茹凤','谭鑫宇','韩耀祖'],\n",
    "    '年龄':[21,22,23],\n",
    "    '地址':['吉林省松原市','吉林省松原市','河北省邯郸市']\n",
    "}\n",
    "df2 = pd.DataFrame(data2)\n",
    "\n",
    "# 将这两个df分别写出到同一个Excel不同的Sheet中\n",
    "with pd.ExcelWriter(path + '多工作表.xlsx') as writer:\n",
    "    df1.to_excel(writer, sheet_name='Sheet1',index=False)\n",
    "    df2.to_excel(writer, sheet_name='Sheet2',index=False)\n",
    "print('写出完毕')"
   ],
   "id": "54e820371858f7c3",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "写出完毕\n"
     ]
    }
   ],
   "execution_count": 18
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-09T06:40:56.190580Z",
     "start_time": "2025-09-09T06:40:56.172717Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# 高级配置\n",
    "with pd.ExcelWriter(path + '多工作表2.xlsx') as writer:\n",
    "    df1.to_excel(writer, sheet_name='明星',index=False,startrow=1)\n",
    "    df2.to_excel(writer, sheet_name='学生',index=False,header=False,startrow=2)"
   ],
   "id": "4f67308088f0c087",
   "outputs": [],
   "execution_count": 20
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-09T07:04:24.639520Z",
     "start_time": "2025-09-09T07:04:24.621226Z"
    }
   },
   "cell_type": "code",
   "source": [
    "from openpyxl import load_workbook\n",
    "data =  {\n",
    "    '姓名':['刘千琪','崔龙腾','李欣桐'],\n",
    "    '年龄':[21,22,23],\n",
    "    '地址':['吉林省松原市','吉林省松原市','河北省邯郸市']\n",
    "}\n",
    "df3 = pd.DataFrame(data)\n",
    "\n",
    "\n",
    "\"\"\"\n",
    "    'replace' (默认值)：删除已存在的工作表，然后创建新的工作表\n",
    "    'new'：创建新的工作表，并在名称后添加数字以避免冲突（如 Sheet1, Sheet2）\n",
    "    'overlay'：在现有工作表的基础上追加内容，保留原有数据\n",
    "\"\"\"\n",
    "with pd.ExcelWriter(path + '学生信息4.xlsx',engine='openpyxl',mode='a', if_sheet_exists='overlay') as writer:\n",
    "    df3.to_excel(writer, index=False, header=False, startrow=5)\n"
   ],
   "id": "57c2346e92e58137",
   "outputs": [],
   "execution_count": 35
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "## 5. 写出数据到数据库",
   "id": "2fffdeee876fd9a7"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-09T07:22:35.493199Z",
     "start_time": "2025-09-09T07:22:34.802283Z"
    }
   },
   "cell_type": "code",
   "source": [
    "from sqlalchemy import create_engine\n",
    "\n",
    "data =  {\n",
    "    '姓名':['刘千琪','崔龙腾','李欣桐'],\n",
    "    '年龄':[21,22,23],\n",
    "    '地址':['吉林省松原市','吉林省松原市','河北省邯郸市']\n",
    "}\n",
    "df = pd.DataFrame(data)\n",
    "\n",
    "# 把df写出到数据库中\n",
    "# 1. 定义一个数据库连接的字符串\n",
    "conn_str = 'mysql+pymysql://root:root@localhost:3306/db05'\n",
    "\n",
    "# 2. 创建数据库引擎\n",
    "engine = create_engine(conn_str)\n",
    "\n",
    "# 3. 将数据写入到数据库\n",
    "df.to_sql(\n",
    "    'student_table',\n",
    "    con=engine,\n",
    "    if_exists='replace', # 如果存在就替换\n",
    "    index=False\n",
    ")"
   ],
   "id": "19ec3afcfca69284",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 36
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-09T07:25:00.959770Z",
     "start_time": "2025-09-09T07:25:00.954355Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# 写出到JSON\n",
    "df.to_json(path + 'stu.json')"
   ],
   "id": "4e45fc1afaf97809",
   "outputs": [],
   "execution_count": 38
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-09T07:26:19.362739Z",
     "start_time": "2025-09-09T07:26:19.351329Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# 写出html\n",
    "df.to_html(path + '学生.html')"
   ],
   "id": "440b452f13a5ebb",
   "outputs": [],
   "execution_count": 39
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
